import sys, os

import numpy as np
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), os.path.pardir)))

from datetime import datetime
import pandas as pd
from sqlalchemy import create_engine
from AppStoreConnect import  AppProduct, ProductTypeIdentifier, Courntries
from AppDB import AppDB
from .columns import ColumnName as cn


def get_inner_purchase_df(inner_purchase_apple_id:str, start_date:datetime, end_date:datetime, parent_app_df:pd.DataFrame=None):
        
    start_date_str = start_date.strftime('%Y-%m-%d')
    end_date_str = end_date.strftime('%Y-%m-%d')
    
    # 创建AppDB实例
    engine = AppDB.engine
    
    # 获取从start_date到end_date的 订阅事件数据
    sales_sql = f'select * from summary_daily where begin_date between "{start_date_str}" and "{end_date_str}" and apple_identifier = "{inner_purchase_apple_id}" and promo_code is null'
    print(sales_sql)
    df = pd.read_sql(sales_sql, engine)
    df.fillna(0, inplace=True)
    # 添加一列proceeds，计算每个国家的proceeds之和
    df[cn.proceeds_usd.value] = df['units'] * df['developer_proceeds'] / df['exchange_rate']
    df[cn.unit_proceeds_usd.value] = df['developer_proceeds'].abs() / df['exchange_rate']
    df[cn.unit_price_usd.value] = df['custom_price'].abs() / df['exchange_rate']
    df[cn.unit_price_local.value] = df['custom_price'].abs()
    
    # 按照country_code分组，一列为units之和，一列为proceeds、一列为developer_proceeds平均值
    df = df.groupby('country_code').agg({'units':'sum', cn.proceeds_usd.value:'sum', cn.unit_proceeds_usd.value:'mean', cn.unit_price_usd.value:'mean', cn.unit_price_local.value:'mean'})
    
    # 将country_code转成alpha3
    df['country_code'] = df.index
    df['country_code'] = df['country_code'].apply(lambda x: Courntries.get_alpha3Code_from_alpha2Code(x))
    df.set_index('country_code', inplace=True)
    
    # 将units列改名成cn.purchase_count.value
    df.rename(columns={'units':cn.purchase_count.value}, inplace=True)
    
    if parent_app_df is not None:
        df = df.merge(parent_app_df, left_index=True, right_index=True, how='outer')
        df.fillna(0, inplace=True)
        df[cn.purchase_rate.value] = df[cn.purchase_count.value] / df[cn.downloads.value]
        df[cn.preceeds_per_download_usd.value] = df[cn.proceeds_usd.value] / df[cn.downloads.value]
        
        # 删除downloads列
        df.drop(columns=[cn.downloads.value], inplace=True)

    return df




def get_app_df(app_apple_id:str, start_date:datetime, end_date:datetime):
    
    start_date_str = start_date.strftime('%Y-%m-%d')
    end_date_str = end_date.strftime('%Y-%m-%d')
    
    # 创建AppDB实例
    engine = AppDB.engine
    
    # 获取从start_date到end_date的 订阅事件数据
    sales_sql = f'select * from summary_daily where begin_date between "{start_date_str}" and "{end_date_str}" and apple_identifier = "{app_apple_id}"'
    print(sales_sql)
    df = pd.read_sql(sales_sql, engine)
    
    
    # 按照country_code分组，计算每个国家apple_identifer为parent_apple_id, 且product_type_identifer在ProductTypeIdentifier.new_install_identifiers中的units之和, 并设置这列的名称为parent_units, country_code为索引

    df = df[df['product_type_identifier'].isin(ProductTypeIdentifier.new_install_identifiers())]
    df = df.groupby('country_code').agg({'units':'sum'})
    
    # 将country_code转成alpha3
    df['country_code'] = df.index
    df['country_code'] = df['country_code'].apply(lambda x: Courntries.get_alpha3Code_from_alpha2Code(x))
    df.set_index('country_code', inplace=True)

    df.columns = [cn.downloads.value]
    
    return df


if __name__ == '__main__':

    
    start_date = datetime(2024, 2, 1)
    end_date = datetime(2024, 2, 15)
    app_df = get_app_df(app_apple_id=AppProduct.Hearty, start_date=start_date, end_date=end_date)
    print("App DataFrame. Country as index. Count: ", app_df.shape[0])
    
    premium_df = get_inner_purchase_df(inner_purchase_apple_id=AppProduct.Hearty_Premium_Version, start_date=start_date, end_date=end_date, parent_app_df=app_df)
    print("Premium DataFrame. Country as index. Count: ", premium_df.shape[0])
    
    
    df = app_df.merge(premium_df, left_index=True, right_index=True, how='outer')

    print(df.head(10))
    
